"""Initial database schema

Revision ID: 001_initial
Revises: 
Create Date: 2024-11-19

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = '001_initial'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 扫描任务表
    op.create_table(
        'scan_tasks',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('task_type', sa.String(length=50), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('status', sa.String(length=20), server_default='enabled', nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name'),
        sa.UniqueConstraint('task_type')
    )
    op.create_index('idx_scan_tasks_type', 'scan_tasks', ['task_type'])
    op.create_index('idx_scan_tasks_status', 'scan_tasks', ['status'])

    # 扫描记录表
    op.create_table(
        'scan_records',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('task_id', sa.Integer(), nullable=False),
        sa.Column('branch', sa.String(length=255), nullable=False),
        sa.Column('scan_time', sa.DateTime(), nullable=False),
        sa.Column('status', sa.String(length=20), nullable=False),
        sa.Column('duration', sa.Integer(), nullable=True),
        sa.Column('scan_params', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column('issue_count', sa.Integer(), server_default='0', nullable=True),
        sa.Column('confirmed_count', sa.Integer(), server_default='0', nullable=True),
        sa.Column('unconfirmed_count', sa.Integer(), server_default='0', nullable=True),
        sa.Column('created_by', sa.String(length=100), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['task_id'], ['scan_tasks.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_scan_records_task_id', 'scan_records', ['task_id'])
    op.create_index('idx_scan_records_branch', 'scan_records', ['branch'])
    op.create_index('idx_scan_records_scan_time', 'scan_records', ['scan_time'])
    op.create_index('idx_scan_records_status', 'scan_records', ['status'])

    # 扫描问题表
    op.create_table(
        'scan_issues',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('record_id', sa.Integer(), nullable=False),
        sa.Column('issue_number', sa.String(length=50), nullable=False),
        sa.Column('rule_name', sa.String(length=200), nullable=False),
        sa.Column('level', sa.String(length=20), nullable=False),
        sa.Column('file_path', sa.String(length=500), nullable=False),
        sa.Column('line_number', sa.Integer(), nullable=True),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('code_snippet', sa.Text(), nullable=True),
        sa.Column('confirm_status', sa.String(length=20), server_default='unconfirmed', nullable=True),
        sa.Column('confirm_type', sa.String(length=50), nullable=True),
        sa.Column('confirmed_by', sa.String(length=100), nullable=True),
        sa.Column('confirmed_at', sa.DateTime(), nullable=True),
        sa.Column('confirm_comment', sa.Text(), nullable=True),
        sa.Column('inherited_from_id', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['inherited_from_id'], ['scan_issues.id'], ),
        sa.ForeignKeyConstraint(['record_id'], ['scan_records.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_scan_issues_record_id', 'scan_issues', ['record_id'])
    op.create_index('idx_scan_issues_level', 'scan_issues', ['level'])
    op.create_index('idx_scan_issues_confirm_status', 'scan_issues', ['confirm_status'])
    op.create_index('idx_scan_issues_file_path', 'scan_issues', ['file_path'])
    op.create_index('idx_scan_issues_inherited_from', 'scan_issues', ['inherited_from_id'])

    # 确认历史表
    op.create_table(
        'confirm_history',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('issue_id', sa.Integer(), nullable=False),
        sa.Column('confirm_type', sa.String(length=50), nullable=False),
        sa.Column('confirmed_by', sa.String(length=100), nullable=False),
        sa.Column('confirmed_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('confirm_comment', sa.Text(), nullable=True),
        sa.Column('action_type', sa.String(length=20), nullable=False),
        sa.Column('from_type', sa.String(length=50), nullable=True),
        sa.Column('to_type', sa.String(length=50), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['issue_id'], ['scan_issues.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_confirm_history_issue_id', 'confirm_history', ['issue_id'])
    op.create_index('idx_confirm_history_confirmed_at', 'confirm_history', ['confirmed_at'])

    # 字段配置表
    op.create_table(
        'field_configs',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('task_id', sa.Integer(), nullable=False),
        sa.Column('field_name', sa.String(length=100), nullable=False),
        sa.Column('display_name', sa.String(length=200), nullable=False),
        sa.Column('field_type', sa.String(length=50), nullable=False),
        sa.Column('field_category', sa.String(length=50), nullable=False),
        sa.Column('display_order', sa.Integer(), server_default='0', nullable=True),
        sa.Column('show_in_list', sa.Boolean(), server_default='false', nullable=True),
        sa.Column('show_in_detail', sa.Boolean(), server_default='true', nullable=True),
        sa.Column('searchable', sa.Boolean(), server_default='false', nullable=True),
        sa.Column('sortable', sa.Boolean(), server_default='false', nullable=True),
        sa.Column('required', sa.Boolean(), server_default='false', nullable=True),
        sa.Column('default_value', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column('help_text', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['task_id'], ['scan_tasks.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('task_id', 'field_name', name='uq_task_field')
    )
    op.create_index('idx_field_configs_task_id', 'field_configs', ['task_id'])
    op.create_index('idx_field_configs_category', 'field_configs', ['field_category'])

    # 扫描记录扩展数据表
    op.create_table(
        'scan_record_extensions',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('record_id', sa.Integer(), nullable=False),
        sa.Column('field_name', sa.String(length=100), nullable=False),
        sa.Column('field_value', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['record_id'], ['scan_records.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('record_id', 'field_name', name='uq_record_field')
    )
    op.create_index('idx_record_extensions_record_id', 'scan_record_extensions', ['record_id'])
    op.create_index('idx_record_extensions_field_name', 'scan_record_extensions', ['field_name'])

    # 扫描问题扩展数据表
    op.create_table(
        'scan_issue_extensions',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('issue_id', sa.Integer(), nullable=False),
        sa.Column('field_name', sa.String(length=100), nullable=False),
        sa.Column('field_value', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['issue_id'], ['scan_issues.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('issue_id', 'field_name', name='uq_issue_field')
    )
    op.create_index('idx_issue_extensions_issue_id', 'scan_issue_extensions', ['issue_id'])
    op.create_index('idx_issue_extensions_field_name', 'scan_issue_extensions', ['field_name'])


def downgrade() -> None:
    op.drop_table('scan_issue_extensions')
    op.drop_table('scan_record_extensions')
    op.drop_table('field_configs')
    op.drop_table('confirm_history')
    op.drop_table('scan_issues')
    op.drop_table('scan_records')
    op.drop_table('scan_tasks')

